USE LoanProcessingSystem
GO
/* ============Loan Type============*/

	-- view loan type all
	CREATE PROC View_all_LoanType
	AS
		SELECT * FROM LoanType
	GO

	-- view loan type enable
	CREATE PROC View_all_LoanType_Enable
	AS
		SELECT * FROM LoanType WHERE [Status] = 1
	GO
	
	--view loan type by id 
	CREATE PROC View_LoanType_By_ID
		@id INT 
	AS
		SELECT * FROM LoanType WHERE LoanTypeID = @id
	GO

	--view loan type by name 
	CREATE PROC View_LoanType_By_Name
		@name VARCHAR(50)
	AS
		SELECT * FROM LoanType WHERE TypeOfLoan LIKE ('%'+@name+'%')
	GO

	--Insert to loan type
	CREATE PROC Insert_LoanType
		@TypeOfLoan VARCHAR(50),
		@InterestRate FLOAT
	AS
		INSERT INTO LoanType VALUES (@TypeOfLoan,@InterestRate,1)
	GO

	--disable , enable loan type
	CREATE PROC Disable_LoanType
		@id INT
	AS
		UPDATE LoanType SET [Status] = 0 WHERE LoanTypeID = @id   
	GO

	CREATE PROC Enable_LoanType
		@id INT
	AS
		UPDATE LoanType SET [Status] = 1 WHERE LoanTypeID = @id   
	GO

	--Update to loan type
	CREATE PROC Update_LoanType
		@id INT,
		@TypeOfLoan VARCHAR(50),
		@InterestRate FLOAT		
	AS
		UPDATE LoanType SET TypeOfLoan = @TypeOfLoan, InterestRate = @InterestRate WHERE LoanTypeID = @id   
	GO

 
/* ============Fine Loan===========*/

	-- view fine loan all
	CREATE PROC View_all_FineLoan
	AS
		SELECT * FROM FineLoan
	GO

	--view fine loan by id 
	CREATE PROC View_FineLoan_By_ID
		@id INT 
	AS
		SELECT * FROM FineLoan WHERE FineID = @id
	GO

	--Insert to fine loan
	CREATE PROC Insert_FineLoan
		@To MONEY,
		@From MONEY,
		@Fine FLOAT
	AS
		INSERT INTO FineLoan VALUES (@To,@From,@Fine)
	GO

	--Delete to fine loan
	CREATE PROC Delete_FineLoan
		@id INT
	AS
		DELETE FROM FineLoan WHERE FineID = @id   
	GO

	--Update to fine loan
	CREATE PROC Update_FineLoan
		@id INT,
		@To MONEY,
		@From MONEY,
		@Fine FLOAT		
	AS
		UPDATE FineLoan SET ToMoney = @To, FromMoney = @From, Fine = @Fine WHERE FineID = @id   
	GO

 
/* ============ Branch ===========*/

	-- view Branch
	CREATE PROC View_all_Branch
	AS
		SELECT * FROM Branch
	GO

	--view Branch by id 
	CREATE PROC View_Branch_By_ID
		@id INT 
	AS
		SELECT * FROM Branch WHERE BranchID = @id
	GO

	CREATE PROC View_Branch_By_Name
		@name VARCHAR(50)
	AS
		SELECT * FROM Branch WHERE BranchName  LIKE ('%'+@name+'%')
	GO
	--Insert to Branch
	CREATE PROC Insert_Branch
		@name		 VARCHAR(100),
		@Address	 VARCHAR(100),
		@PhoneNumber VARCHAR(15),
		@Description VARCHAR(100),
		@Status		 BIT
	AS
		INSERT INTO Branch 
		VALUES(@name,@Address,@PhoneNumber,@Description,@Status)
	GO

	--Delete to Branch
	CREATE PROC Disable_Branch
		@id INT
	AS
		UPDATE Branch 
		SET Status = 0
		WHERE BranchID = @id   
	GO
	
	CREATE PROC Enable_Branch
		@id INT
	AS
		UPDATE Branch 
		SET Status = 1
		WHERE BranchID = @id   
	GO
	--Update to fine loan
	CREATE PROC Update_Branch
		@id INT,
		@name		 VARCHAR(100),
		@Address	 VARCHAR(100),
		@PhoneNumber VARCHAR(15),
		@Description VARCHAR(100),
		@Status		 BIT		
	AS
		UPDATE Branch 
		SET	BranchName = @name,
			Address = @Address, 
			PhoneNumber = @PhoneNumber,
			Description = @Description,
			Status	= @Status 
		WHERE BranchID = @id   
	GO

/*========== USER ===========*/

GO

CREATE PROC sp_CheckLogin
@user nvarchar(30),
@pass nvarchar(30)
AS
SELECT U.*, UT.*,B.*
FROM UserType UT INNER JOIN [USER] U ON UT.typeID = U.TypeID
INNER JOIN Branch B ON U.BranchID = B.BranchID
WHERE		
U.UserName = @user And 
U.passWord = @pass And 
U.isLock = 0 And U.[status] = 0

GO

-- PROC UPDATE PASSWORD
CREATE PROC sp_ChangePass
@UserName nvarchar(50),
@OldPassword nvarchar(50),
@NewPassword nvarchar(50)
AS
UPDATE [User]
SET Password = @NewPassword
WHERE userName = @UserName and 
password = @OldPassword

GO
-- PROC GET ALL USER
CREATE PROC sp_getUserAll
AS
Select U.*,typeName,BranchName
From [User] U INNER JOIN Branch B
on U.BranchId = B.BranchId INNER JOIN UserType UT on U.TypeId = UT.TypeId 
WHERE U.status = 0

GO
/*CREATE PROC sp_getUserAll
AS
Select U.*,typeName,BranchName
From [User] U INNER JOIN Branch B
on U.BranchId = B.BranchId INNER JOIN UserType UT on U.TypeId = UT.TypeId 
WHERE U.status = 0

GO
*/
--PROC INSERT NEW USER
CREATE PROC sp_InsertUser
@userName nvarchar(30), 
@fullName nvarchar(100),
@address nvarchar(200),
@birthDay smalldatetime,
@email nvarchar(100),
@identity nchar(12),
@phone nchar(12),
@sex bit,
@typeUser int,
@branchId int,
@note nvarchar(500)
AS
Insert into [User](UserName,Password, FullName, Address, BirthDay, Email, [Identity], phone, sex, typeId, branchId, note, isLock, status)
values(@userName,'123456',@fullName,@address,@birthDay,@Email,@identity,@phone,@sex,@typeUser,@branchId,@note,0,0)

GO
-- PROC UPDATE USER
CREATE PROC sp_updateUser
@Id int,
@userName nvarchar(30),
@fullName nvarchar(100),
@address nvarchar(200),
@birthDay datetime,
@email nvarchar(100),
@identity nchar(12),
@phone nchar(12),
@sex bit,
@typeId int,
@branchId int,
@note nvarchar(500)
AS
UPDATE [USER]
SET
UserName = @userName,
fullName = @fullName,
Address = @Address,
birthDay = @birthDay,
Email = @Email,
[identity] =@identity,
phone =@phone,
sex = @sex,
typeId = @typeId,
branchId = @branchId,
note = @note
WHERE UserID = @Id

GO
--Delete to User
CREATE PROC sp_DeleteUser
@id INT
AS
UPDATE [USER] 
SET	status = 1
WHERE UserID = @id

GO 

--CHECK USER
CREATE PROC sp_CheckUser
@userName nvarchar(50)
AS
SELECT username 
from [User] 
where username = @userName and status = 0

GO

CREATE PROC sp_CheckIdentity
@identity nvarchar(10)
AS
SELECT [identity]
from [User] 
where [identity] = @identity and status = 0

GO
--LOCK USER
CREATE PROC sp_LockUser
@id INT
AS
UPDATE [User] 
SET isLock = 1
WHERE UserId =@id

GO
--UNLOCK USER
CREATE PROC sp_UnLockUser
@id INT
AS
UPDATE [User] 
SET isLock = 0
WHERE UserId =@id
GO 

--View User by id 
	CREATE PROC sp_getUserByID
		@id int 
	AS
		Select U.*,TypeName,BranchName , PhoneNumber, B.[Address] as BAddress , B.[Description] , B.Status as BStatus
		From [User] U INNER JOIN Branch B
		on U.BranchId = B.BranchId INNER JOIN UserType UT on U.TypeId = UT.TypeId 
		where U.UserID = @id
	GO

--SEARCH By Usernam
CREATE PROC sp_getUserByUserName
		@usename varchar(50) 
	AS
		Select U.*,TypeName,BranchName , PhoneNumber, B.[Address] as BAddress , B.[Description] , B.Status as BStatus
		From [User] U INNER JOIN Branch B
		on U.BranchId = B.BranchId INNER JOIN UserType UT on U.TypeId = UT.TypeId 
		where U.UserName like ('%' + @usename + '%')
	GO

--SEARCH BY FULL NAME
CREATE PROC sp_getUserByFullName
		@fullname varchar(50) 
	AS
		Select U.*,TypeName,BranchName , PhoneNumber, B.[Address] as BAddress , B.[Description] , B.Status as BStatus
		From [User] U INNER JOIN Branch B
		on U.BranchId = B.BranchId INNER JOIN UserType UT on U.TypeId = UT.TypeId 
		where U.fullName like '%' + @fullname + '%'
	GO

-- SEARCH BY IDENTITY
CREATE PROC sp_getUserByIdentity
@identity varchar(9)
AS
	Select U.*,TypeName,BranchName , PhoneNumber, B.[Address] as BAddress , B.[Description] , B.Status as BStatus
	From [User] U INNER JOIN Branch B
	on U.BranchId = B.BranchId INNER JOIN UserType UT on U.TypeId = UT.TypeId 
	where U.[identity] like @identity
GO

CREATE PROC sp_getUserByLock
@isLock int
AS
Select U.*,TypeName,BranchName , PhoneNumber, B.[Address] as BAddress , B.[Description] , B.Status as BStatus
From [User] U INNER JOIN Branch B
on U.BranchId = B.BranchId INNER JOIN UserType UT on U.TypeId = UT.TypeId 
where U.isLock =@isLock
/*======================USERTYPE=======================*/

GO
CREATE PROC sp_getUserType
AS
SELECT * FROM UserType

GO
/*======================LoanContact====================*/
	-- view all Loan contact
	
	

	CREATE PROC View_all_LoanContact
	AS
		SELECT * FROM LoanContact
	GO
	
	-- view loan contact by id
	CREATE PROC View_LoanContact_By_ID
		@id int 
	AS
		SELECT * FROM LoanContact WHERE LoanID = @id
	GO
	
	-- View loan by Amount
	CREATE PROC View_LoanContact_By_Amount
		@fromAmount MONEY,
		@toAmount MONEY
	AS
		SELECT * FROM LoanContact WHERE Amount BETWEEN @fromAmount AND @toAmount
	GO
	-- view loan by date
	CREATE PROC View_LoanContact_By_Date
		@fromDate DATETIME,
		@toDATE DATETIME
	AS
		SELECT * FROM LoanContact WHERE DateStart BETWEEN @fromDate AND @toDATE
	GO
	-- view Loan contact by Customer
	CREATE PROC View_LoanContact_By_CustomerID
		@customerID int
	AS
		SELECT * FROM LoanContact WHERE CustomerID = @customerID
	GO
	
	-- view Loan contact by Customer
	CREATE PROC View_LoanContact_By_Branch
		@userID INT
	AS
		SELECT * FROM LoanContact 
		WHERE UserID IN 
			(SELECT UserID FROM [User] 
				WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID)) 
	GO
	-- 
	CREATE PROC View_LoanContact_By_ID_Branch
		@userID INT,
		@id INT
	AS
		SELECT * FROM LoanContact 
		WHERE LoanID = @id AND UserID IN 
			(SELECT UserID FROM [User] 
				WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID)) 
	GO
	
	-- branch and amount
	CREATE PROC View_LoanContact_By_Amount_Branch
		@userID INT,
		@fromAmount MONEY,
		@toAmount MONEY
	AS
		SELECT * FROM LoanContact 
		WHERE  (Amount BETWEEN @fromAmount AND @toAmount) AND UserID IN 
			(SELECT UserID FROM [User] 
				WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID)) 
	GO
	
	CREATE PROC View_LoanContact_By_Date_Branch
		@userID INT,
		@fromDate DATETIME,
		@toDate DATETIME
	AS
		SELECT * FROM LoanContact 
		WHERE  (DateStart BETWEEN @fromDate AND @toDate) AND UserID IN 
			(SELECT UserID FROM [User] 
				WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID)) 
	GO
	--view by customer branch 
	CREATE PROC View_LoanContact_By_CustomerID_Branch
		@userid INT,
		@customerID INT
		
	AS
		SELECT * FROM LoanContact 
		WHERE  CustomerID = @customerID AND UserID IN 
			(SELECT UserID FROM [User] 
				WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID)) 
	GO
	
	CREATE PROC View_LoanContact_All
		@select INT
	AS
		IF(@select = 1 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact	
			)
			AS l WHERE l.LoanTypeID = 1
		END
		
		IF(@select = 2 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact	
			)
			AS l WHERE l.LoanTypeID = 2
		END
		
		IF(@select = 3 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact	
			)
			AS l WHERE l.LoanTypeID = 3
		END
		
		IF(@select = 4 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact	
			)
			AS l WHERE l.LoanTypeID = 4
		END
		
		IF(@select = 5 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact	
			)
			AS l WHERE l.IsAccepted = 2
		END
		
		IF(@select = 6 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact	
			)
			AS l WHERE l.IsAccepted = 1
		END
		
		IF(@select = 7 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact	
			)
			AS l WHERE l.LoanTypeID = 0
		END
	GO
	
	CREATE PROC View_LoanContact_Branch
		@userID INT,
		@select INT
	AS
		IF(@select = 1 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact 
					WHERE UserID IN 
					(SELECT UserID FROM [User] 
						WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID))	
			)
			AS l WHERE l.LoanTypeID = 1
		END
		
		IF(@select = 2 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact 
					WHERE UserID IN 
					(SELECT UserID FROM [User] 
						WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID))	
			)
			AS l WHERE l.LoanTypeID = 2
		END
		
		IF(@select = 3 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact 
					WHERE UserID IN 
					(SELECT UserID FROM [User] 
						WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID))	
			)
			AS l WHERE l.LoanTypeID = 3
		END
		
		IF(@select = 4 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact 
					WHERE UserID IN 
					(SELECT UserID FROM [User] 
						WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID))	
			)
			AS l WHERE l.LoanTypeID = 4
		END
		
		IF(@select = 5 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact 
					WHERE UserID IN 
					(SELECT UserID FROM [User] 
						WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID))	
			)
			AS l WHERE l.IsAccepted = 2
		END
		
		IF(@select = 6 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact 
					WHERE UserID IN 
					(SELECT UserID FROM [User] 
						WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID))	
			)
			AS l WHERE l.IsAccepted = 1
		END
		
		IF(@select = 7 )
		BEGIN 
			SELECT * FROM (
				SELECT * FROM LoanContact 
					WHERE UserID IN 
					(SELECT UserID FROM [User] 
						WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID))	
			)
			AS l WHERE l.LoanTypeID = 0
		END
	GO
	
	CREATE PROC Insert_LoanContact
		@CustomerID		INT,
		@LoanTypeID		INT,
		@UserID			INT,
		@Amount			MONEY,
		@Period			INT,
		@dateStart		DATETIME
	AS
		INSERT INTO LoanContact VALUES(@CustomerID,@LoanTypeID,@UserID,null,@Amount,@Period,@dateStart,null,2)
	GO
	-- calculate							
	CREATE PROC Cal_Payment
		@loanID INT,
		@loanTypeID INT
	AS
		DECLARE @months INT
		DECLARE @IntestRate FLOAT
		DECLARE @payment MONEY
		DECLARE @DateStart DATETIME
		DECLARE @Amount Money
		
		SELECT @IntestRate = (InterestRate/12) FROM LoanType WHERE LoanTypeID = @loanTypeID
		
		SELECT	@months = Period ,
				@payment = (Amount/@months),
				@DateStart = DateStart,
				@Amount = Amount
		FROM LoanContact 
		WHERE LoanID = @loanID
		
		DECLARE @i INT
		SET @i = 0
		
		WHILE @i < @months
		BEGIN
			SET @i = @i + 1
			DECLARE @Interest MONEY
			SET @Interest = (@Amount - (@i-1)*@payment)*@IntestRate*0.01
			
			INSERT INTO Payment Values 
			(
				
				@loanID,@i,@payment,@Interest,0,
				@payment+@Interest,
				DATEADD(MONTH,@i,@DateStart),null,
				0
			)
		END
		
		GO
		
		--payment
		CREATE PROC Update_Payment
			@paymentID INT
		AS
			UPDATE Payment SET Status = 1 , DatePaid = GETDATE() WHERE PaymentID = @paymentID
		GO
		
		--view and fine
		CREATE PROC View_Payment
			@loanID INT
		AS
			SELECT * FROM Payment WHERE LoanID  = @loanID
		GO
		-- cal Fine
		CREATE PROC Cal_Fine
			@loanID INT
		AS
			DECLARE @Months INT
			SELECT @Months = Period FROM LoanContact WHERE LoanID  = @loanID
			DECLARE @i INT
			SET @i = 0
			
			WHILE @i <= @months
			BEGIN 
				SET @i = @i+1
				DECLARE @DureDate DATETIME
				DECLARE @Status BIT
				DECLARE @payment MONEY
				DECLARE @Interest MONEY
				DECLARE @fineAmount MONEY
				
				SELECT @DureDate = DureDate, @Status = [Status],@payment = Payment, @Interest = Interest , @fineAmount = FineAmount 
				FROM Payment
				WHERE LoanID = @loanID AND [Month] = @i
				
				
				
				IF((GETDATE()> @DureDate) AND @Status = 0 AND @fineAmount = 0)
				BEGIN
					DECLARE @count INT
					SELECT @count = COUNT(*) FROM FineLoan
					DECLARE @fine FLOAT
					DECLARE @j INT
					SET @j = 0
					
				    WHILE @j<@count
				    BEGIN
						SET @j = @j+1
						DECLARE @from MONEY
						DECLARE @to MONEY
						DECLARE @fines FLOAT
						SELECT @from = FromMoney , @to = ToMoney , @fines = Fine FROM FineLoan 
						WHERE FineID = @j		
						IF @to = 0
						BEGIN
							IF @payment >= @from
							BEGIN 
								SET @fine = @fines
							END
						END
						
						ELSE
						BEGIN
							IF( @payment >= @from AND @payment <= @to)
							BEGIN 
								SET @fine = @fines
							END
						END
						
				    END
					UPDATE Payment SET FineAmount = (@payment*@fine*0.01) , TotalPay = (@payment + @payment*@fine*0.01+@Interest)
					WHERE LoanID  = @loanID AND [Month] = @i
				END
			END
			
			
	GO
		
	CREATE PROC Accepted_LoanContact
		@loanID INT,
		@userID INT
	AS
		UPDATE LoanContact 
		SET IsAccepted = 1 , UserAccecpted = @userID ,DateApproved = GETDATE()
		WHERE LoanID = @loanID 
	GO
	
	CREATE PROC NotAccepted_LoanContact
		@loanID INT,
		@userID INT
	AS
		UPDATE LoanContact 
		SET IsAccepted = 0 , UserAccecpted = @userID ,DateApproved = GETDATE()
		WHERE LoanID = @loanID 
	GO
	
	CREATE PROC Cal_TotalAmount
		
	AS
		SELECT SUM(Amount) AS TotalAmount FROM LoanContact
	GO
	
	CREATE PROC Cal_TotalFine
	AS
		SELECT SUM(FineAmount) AS [TotalFine] FROM Payment
	GO
	
	CREATE PROC Cal_Interest
	AS
		SELECT SUM(Interest) AS [TotalInterest] FROM Payment
	GO
	
	CREATE PROC Cal_TotalPayment
	AS
		SELECT SUM(TotalPay) AS [TotalPayment] FROM Payment
	GO
	
	CREATE PROC Cal_TotalLoan
	AS
		SELECT Count(*) AS [TotalLoan] FROM LoanContact
	GO
	
	
/*=================Customer==============*/
	-- view all customer
	CREATE PROC View_all_Customer
	AS
		SELECT * FROM Customer
	GO
	
	-- view Customer by id
	CREATE PROC View_Customer_By_ID
		@id int 
	AS
		SELECT * FROM Customer WHERE CustomerID = @id
	GO
	
	
	-- view customer  in branch 
	CREATE PROC View_Customer_By_Branch
		@userID INT
	AS
		SELECT * FROM Customer 
		WHERE UserID IN 
			(SELECT UserID FROM [User] 
				WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID)) 
	GO

	
	-- view customer in branch 
	CREATE PROC View_Customer_By_ID_Branch
		@customerID INT,		
		@userID INT
	AS
		SELECT * FROM Customer 
		WHERE CustomerID =  @customerID AND UserID IN 
			(SELECT UserID FROM [User] 
				WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID)) 
	GO
	
	--view customer by name 
	CREATE PROC View_Customer_By_Name_Branch
		@userID INT,
		@customerName VARCHAR(100) 
	AS
		SELECT * FROM Customer 
		WHERE  (FirstName LIKE ('%' + @customerName + '%')  OR LastName LIKE ('%' + @customerName + '%'))  AND 
			UserID IN (SELECT UserID FROM [User] 
				WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID)) 
		GO  
		
	--search customer by ID
CREATE PROC View_Customer_By_Name
		@customerName VARCHAR(100) 
	AS
		SELECT * FROM Customer 
		WHERE  (FirstName LIKE ('%' + @customerName + '%')  OR FirstName LIKE ('%' + @customerName + '%'))
	GO

-- Search customer by organzination
CREATE PROC View_Customer_By_Organzination
@org varchar(100)
AS
SELECT * FROM Customer
WHERE Organzination like ('%' + @org + '%')

GO

CREATE PROC View_Customer_By_Organzination_Branch
@org varchar(100),
@userID int
AS
SELECT * FROM Customer
WHERE Organzination like ('%' + @org + '%') AND
 UserID IN (SELECT UserID FROM [User] 
				WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID)) 



GO
--Search customer bu identity
CREATE PROC View_Customer_By_identity
@identity varchar(100)
AS
SELECT * FROM Customer
WHERE @identity = @identity

GO

CREATE PROC View_Customer_By_identity_Branch
@identity varchar(100),
@userID int
AS
SELECT * FROM Customer
WHERE @identity = @identity AND
UserID IN (SELECT UserID FROM [User] 
				WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID)) 

GO

--Search customer bu Gross
CREATE PROC View_Customer_By_Gross
@gross float
AS
SELECT * FROM Customer
WHERE GrossSalary = @gross

GO

CREATE PROC View_Customer_By_Gross_Branch
@gross float,
@userID int
AS
SELECT * FROM Customer
WHERE GrossSalary = @gross AND
UserID IN (SELECT UserID FROM [User] 
				WHERE BranchID = (SELECT BranchID FROM [User] WHERE UserID = @userID)) 

GO

	-- insert Customer 
	CREATE PROC Insert_Customer
		@UserID			INT,
		@firstName		VARCHAR(50),
		@LastName		VARCHAR(50),
		@address		VARCHAR(100),
		@birthday		SMALLDATETIME,
		@PhoneNumber	VARCHAR(15),
		@Email			VARCHAR(100),
		@Identity		CHAR(9),
		@Sex			BIT,
		@Organzination	VARCHAR(100),
		@GrossSalary	MONEY,
		@Note			VARCHAR(500),
		@Status			BIT 
		
	AS
		INSERT INTO Customer VALUES (	@UserID,@firstName,@LastName , @address,
										@birthday,@PhoneNumber,@Email,@Identity,
										@Sex,@Organzination,@GrossSalary,@Note,@Status
									)
	GO
	
	-- update customer
	CREATE PROC Update_Customer
		@CustomerID		INT,
		@UserID			INT,
		@firstName		VARCHAR(50),
		@LastName		VARCHAR(50),
		@address		VARCHAR(100),
		@birthday		SMALLDATETIME,
		@PhoneNumber	VARCHAR(15),
		@Email			VARCHAR(100),
		@Identity		CHAR(9),
		@Sex			BIT,
		@Organzination	VARCHAR(100),
		@GrossSalary	MONEY,
		@Note			VARCHAR(500)		
	AS
		UPDATE Customer 
							SET UserID	= @UserID,
							FirstName	= @firstName,
							LastName	= @LastName , 
							[Address]	= @address,
							BrithDay	= @birthday,
							PhoneNumber	= @PhoneNumber,
							Email		= @Email,
							[Identity] = @Identity,
							Sex			= @Sex,
							Organzination = @Organzination,
							GrossSalary = @GrossSalary,
							Note		= @Note
		WHERE CustomerID = @CustomerID				
	GO
			
	-- Disable Customer
	CREATE PROC Disable_Customer
		@CustomerID INT
	AS
		UPDATE Customer SET [Status] = 1 
		WHERE CustomerID = @CustomerID
	GO
	
	CREATE PROC Enable_Customer
		@CustomerID INT
	AS
		UPDATE Customer SET [Status] = 0 
		WHERE CustomerID = @CustomerID
	GO
	CREATE PROC cal_totalCustomer
		
	AS
		SELECT COUNT(*) AS [TotalCustomer] FROM Customer
	GO